#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

--'每天/每月/每年/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_origin_nd
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    group_type,
    cou_id
FROM tmp
where group_type='year_line_nd' OR group_type='month_line_nd' OR group_type='day_line_nd';

--'每天/每月/每年/各地区/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_area_origin_nd
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    area,
    group_type,
    cou_id
FROM tmp
where group_type='year_area_line_nd' OR group_type='month_area_line_nd' OR group_type='day_area_line_nd';

--'每天/每月/每年/各学科/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_sub_origin_nd

WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    sub_name,
    group_type,
    cou_id
FROM tmp
where group_type='year_sub_line_nd' OR group_type='month_sub_line_nd' OR group_type='day_sub_line_nd';

--'每天/每月/每年/各校区/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_sch_origin_nd
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    sch_name,
    group_type,
    cast(cast(cou_id AS varchar) AS integer) as cou_id
FROM tmp
where group_type='year_sch_line_nd' OR group_type='month_sch_line_nd' OR group_type='day_sch_line_nd';

--'每天/每月/每年/各来源渠道/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_oc_origin_nd
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    origin_channel,
    group_type,
    cou_id
FROM tmp
where group_type='year_oc_line_nd' OR group_type='month_oc_line_nd' OR group_type='day_oc_line_nd';

--'每天/每月/每年/各咨询中心/线上线下/新老客户的意向用户个数统计'
insert into hive.edu_dws.dws_dt_dep_origin_nd
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    Y,
    M,
    D,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    dep_name,
    group_type,
    cou_id
FROM tmp
where group_type='year_dep_line_nd' OR group_type='month_dep_line_nd' OR group_type='day_dep_line_nd';

--'每天/每小时/线上线下/新老客户的有效线索个数和有效率统计'
insert into hive.edu_dws.dws_dt_line_nd_sta
WITH tmp AS (
    select
        create_time_year AS Y,--年
        create_time_month AS M,--月
        dt AS D,--日
        create_time_hour AS H,--小时
        area,--地区
        sch_name,--校区
        sub_name,--学科
        origin_channel,--来源渠道
        dep_name,--咨询中心
        origin_type,--线上线下
        clue_state,--新老学员
        appeal_status,--申诉状态
        count(id) as cou_id,
        case when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2041
             then 'year_line_nd'--每年线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1017
             then 'month_line_nd'--每月线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 505
             then 'day_line_nd'--每天线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1913
             then 'year_area_line_nd'--每年各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 889
             then 'month_area_line_nd'--每月各地区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 377
             then 'day_area_line_nd'--每天各地区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2009
             then 'year_sub_line_nd'--每年各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 985
             then 'month_sub_line_nd'--每月各学科线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 473
             then 'day_sub_line_nd'--每天各学科线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1977
             then 'year_sch_line_nd'--每年各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 953
             then 'month_sch_line_nd'--每月各校区线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 441
             then 'day_sch_line_nd'--每天各校区线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2025
             then 'year_oc_line_nd'--每年各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1001
             then 'month_oc_line_nd'--每月各来源渠道线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 489
             then 'day_oc_line_nd'--每天各来源渠道线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 2033
             then 'year_dep_line_nd'--每年各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 1009
             then 'month_dep_line_nd'--每月各咨询中心线上线下新老客户
             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 497
             then 'day_dep_line_nd'--每天各咨询中心线上线下新老客户

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 504
             then 'day_line_nd_sta'--每天线上线下新老客户有效

             when grouping (create_time_year,create_time_month,dt,create_time_hour,area,sch_name,sub_name,origin_channel,dep_name,origin_type,clue_state,appeal_status) = 248
             then 'hour_dep_line_nd'--每小时线上线下新老学员有效
             else 'others'
             end as group_type



    from hive.edu_dwb.dwb_purpose_clue_analyse
    group by
    grouping sets (
    (create_time_year),
    (create_time_year,origin_type,clue_state),--年+线上线下+新老学员
    (create_time_year,create_time_month,origin_type,clue_state),--月+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_type,clue_state),--日+线上线下+新老学员

    (create_time_year,area,origin_type,clue_state),--年+各地区+线上线下+新老学员
    (create_time_year,create_time_month,area,origin_type,clue_state),--月+各地区+线上线下+新老学员
    (create_time_year,create_time_month,dt,area,origin_type,clue_state),--日+各地区+线上线下+新老学员

    (create_time_year,sub_name,origin_type,clue_state),--年+各学科+线上线下+新老学员
    (create_time_year,create_time_month,sub_name,origin_type,clue_state),--月+各学科+线上线下+新老学员
    (create_time_year,create_time_month,dt,sub_name,origin_type,clue_state),--日+各学科+线上线下+新老学员

    (create_time_year,sch_name,origin_type,clue_state),--年+各校区+线上线下+新老学员
    (create_time_year,create_time_month,sch_name,origin_type,clue_state),--月+各校区+线上线下+新老学员
    (create_time_year,create_time_month,dt,sch_name,origin_type,clue_state),--日+各校区+线上线下+新老学员

    (create_time_year,origin_channel,origin_type,clue_state),--年+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,origin_channel,origin_type,clue_state),--月+各来源渠道+线上线下+新老学员
    (create_time_year,create_time_month,dt,origin_channel,origin_type,clue_state),--日+各来源渠道+线上线下+新老学员

    (create_time_year,dep_name,origin_type,clue_state),--年+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dep_name,origin_type,clue_state),--月+各咨询中心+线上线下+新老学员
    (create_time_year,create_time_month,dt,dep_name,origin_type,clue_state),--日+各咨询中心+线上线下+新老学员

    (create_time_year,create_time_month,dt,origin_type,clue_state,appeal_status),--日+线上线下+新老学员+有效线索

    (create_time_year,create_time_month,dt,create_time_hour,origin_type,clue_state,appeal_status)--小时+线上线下+新老学员+有效线索--年+线上线下+新老学员
    ))
SELECT
    y,
    M,
    D,
    H,
    case when origin_type='online' and clue_state='new_stu'
         then '线上新学员'
         when origin_type='online' and clue_state='old_stu'
         then '线上老学员'
         when origin_type='Offline' and clue_state='new_stu'
         then '线下新学员'
         when origin_type='Offline' and clue_state='old_stu'
         then '线下老学员'
         end as or_sta,
    appeal_status,
    group_type,
    cou_id
FROM tmp
where group_type='day_line_nd_sta' OR group_type='hour_dep_line_nd';